/**
 * 
 */
package cs342.project1.mathNotebook.db;

import java.lang.ref.WeakReference;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author aaron
 *
 */
public class DBNotebook extends DBObject {
	
	protected static Map<DatabaseContext, Map<Integer, WeakReference<DBNotebook>>> initializedContexts = new HashMap<DatabaseContext, Map<Integer, WeakReference<DBNotebook>>>();
	
	public static final String NOTEBOOKS_TABLE_NAME           	= "notebooks";
	public static final String NOTEBOOKS_TABLECOLUMN_ID       	= "id";
	public static final String NOTEBOOKS_TABLECOLUMN_DATA     	= "data";
	public static final String NOTEBOOKS_TABLECOLUMN_LASTUPD  	= "last_updated";
	public static final String NOTEBOOKS_TABLECOLUMN_OWNER    	= "owner_username";
	public static final String NOTEBOOKS_TABLECOLUMN_LOCKUSER 	= "lock_username";
	public static final String NOTEBOOKS_TABLECOLUMN_LOCKDATE 	= "lock_date";
	protected static final String NOTEBOOKS_TABLE_DDL 	= "CREATE TABLE IF NOT EXISTS %s (" +
															NOTEBOOKS_TABLECOLUMN_ID +       " INT(32)     NOT NULL AUTO_INCREMENT PRIMARY KEY, "+
															NOTEBOOKS_TABLECOLUMN_DATA +     " BLOB        NOT NULL, " +
															NOTEBOOKS_TABLECOLUMN_LASTUPD +  " DATETIME    NOT NULL, " +
															NOTEBOOKS_TABLECOLUMN_OWNER +    " VARCHAR(50) NOT NULL " + 
															" REFERENCES %s(" + DBPerson.PERSON_TABLECOLUMN_USERNAME + ") " +
															"  ON DELETE CASCADE ON UPDATE CASCADE, " +
															NOTEBOOKS_TABLECOLUMN_LOCKUSER + " VARCHAR(50) " +
															" REFERENCES %s(" + DBPerson.PERSON_TABLECOLUMN_USERNAME + ") " +
															"  ON DELETE SET NULL ON UPDATE CASCADE, " +
															NOTEBOOKS_TABLECOLUMN_LOCKDATE + " DATETIME) "; // arg0 := notebook table name; arg1 := person table name; arg2 := person table name
	
	public static final String READERS_TABLE_NAME              = "readers";
	public static final String READERS_TABLECOLUMN_USERNAME    = "username";
	public static final String READERS_TABLECOLUMN_NOTEBOOK_ID = "notebook_id";
	public static final String READERS_TABLECOLUMN_KEY         = "encrypted_key";
	
	protected static final String READERS_TABLE_DDL		 = "CREATE TABLE IF NOT EXISTS  %s (" +
															READERS_TABLECOLUMN_USERNAME + " VARCHAR(50) NOT NULL " +
															" REFERENCES %s (" + DBPerson.PERSON_TABLECOLUMN_USERNAME + ") " +
															"  ON DELETE CASCADE ON UPDATE CASCADE, " +
															READERS_TABLECOLUMN_NOTEBOOK_ID + " INT(32) NOT NULL " +
															" REFERENCES %s(" + NOTEBOOKS_TABLECOLUMN_ID + ") " +
															"  ON DELETE CASCADE ON UPDATE CASCADE, " +
															READERS_TABLECOLUMN_KEY + " BLOB NOT NULL)"; // arg0 := readers table name; arg1 := person table name; arg2 := notebook table name
	
	public static final String WRITERS_TABLE_NAME              = "writers";
	public static final String WRITERS_TABLECOLUMN_USERNAME    = "username";
	public static final String WRITERS_TABLECOLUMN_NOTEBOOK_ID = "notebook_id";
	
	protected static final String WRITERS_TABLE_DDL 	= "CREATE TABLE IF NOT EXISTS %s (" +
															WRITERS_TABLECOLUMN_USERNAME + " VARCHAR(50) NOT NULL " +
															" REFERENCES %s(" + DBPerson.PERSON_TABLECOLUMN_USERNAME + ") " +
															"  ON DELETE CASCADE ON UPDATE CASCADE, " +
															"notebook_id INT(32) NOT NULL " +
															" REFERENCES %s(" + NOTEBOOKS_TABLECOLUMN_ID + ") " +
															"ON DELETE CASCADE ON UPDATE CASCADE)"; // arg0 := writers table name; arg1 := person table name; arg2 = notebook table name
	
	protected static final String GET_ALL_INSTANCES_SQL = "SELECT " + NOTEBOOKS_TABLECOLUMN_ID + ", " +
															NOTEBOOKS_TABLECOLUMN_LASTUPD + ", " +
															NOTEBOOKS_TABLECOLUMN_OWNER + ", " +
															NOTEBOOKS_TABLECOLUMN_LOCKUSER + ", " +
															NOTEBOOKS_TABLECOLUMN_LOCKDATE + " FROM %s"; // arg0 := writers table name
	protected static final String GET_INSTANCE_SQL 		= GET_ALL_INSTANCES_SQL + " WHERE " +
															NOTEBOOKS_TABLECOLUMN_ID + " = ?"; //arg0 := notebook tbl name; param1 := obj id
	protected static final String GET_DATA_SQL 			= "SELECT " + NOTEBOOKS_TABLECOLUMN_DATA + 
															" FROM %s WHERE " + NOTEBOOKS_TABLECOLUMN_ID + " = ?"; //arg0 := nb tbl name; param1 := nb id
	protected static final String SET_DATA_SQL 			= "UPDATE %s SET " + NOTEBOOKS_TABLECOLUMN_DATA + 
															" = ? WHERE " + NOTEBOOKS_TABLECOLUMN_ID + " = ?"; //arg0 := nb tbl name; param1 := new data param2 := nb id

	protected static final String UPDATE_LOCK_SQL		= "UPDATE %s SET " + 
															NOTEBOOKS_TABLECOLUMN_LOCKUSER + " = ?, " +
															NOTEBOOKS_TABLECOLUMN_LOCKDATE + " = ? WHERE " +
															NOTEBOOKS_TABLECOLUMN_ID + " = ?"; //arg0 := notebook table name; param1 := username; param2 := date; param3 := db notebook id
	protected static final String INSERT_INSTANCE_SQL	= "INSERT INTO %s (" +
															NOTEBOOKS_TABLECOLUMN_OWNER + ", " +
															NOTEBOOKS_TABLECOLUMN_LASTUPD + ", " +
															NOTEBOOKS_TABLECOLUMN_DATA + ") VALUES(?,?,?)"; //arg0 := notebook table name; param1 := ownerUsername; param2 := lastUpdated; param3 := data
	protected static final String GET_ALL_READERS_SQL	= "SELECT " + READERS_TABLECOLUMN_USERNAME + ", " +
															READERS_TABLECOLUMN_NOTEBOOK_ID + ", " +
															READERS_TABLECOLUMN_KEY + " FROM %s"; //arg0 := readers table name
	protected static final String GET_ALL_NB_READER_SQL	= GET_ALL_READERS_SQL + " WHERE " + READERS_TABLECOLUMN_USERNAME + " = ?"; //arg0 := readers table name; param1 := username 
	protected static final String GET_READERS_SQL		= GET_ALL_READERS_SQL + " WHERE " +
															READERS_TABLECOLUMN_NOTEBOOK_ID + " = ?"; //arg0 := readers table name; param1 := notebook id
	protected static final String GET_READER_KEY_SQL	= GET_READERS_SQL + " AND " + 
															READERS_TABLECOLUMN_USERNAME + " = ?"; // arg0 := reader table name; param1 := nb id; param2 : username
	protected static final String GET_READER_SQL		= GET_ALL_READERS_SQL + " WHERE " +
															READERS_TABLECOLUMN_USERNAME + " = ?"; // arg0 := readers table name; param1 := username;
	protected static final String GET_ALL_WRITERS_SQL	= "SELECT " + WRITERS_TABLECOLUMN_USERNAME + ", " +
															WRITERS_TABLECOLUMN_NOTEBOOK_ID + " FROM %s"; //arg0 := WRITERs table name
	protected static final String GET_WRITERS_SQL		= GET_ALL_WRITERS_SQL + " WHERE " +
															WRITERS_TABLECOLUMN_NOTEBOOK_ID + " = ?"; //arg0 := WRITERs table name; param1 := notebook id
	protected static final String GET_IS_WRITER_SQL		= GET_WRITERS_SQL + " AND " +
															WRITERS_TABLECOLUMN_USERNAME + " = ?";//arg0 := WRITERs table name; param1 := notebook id; param2 := username
	protected static final String GET_WRITER_SQL		= GET_ALL_WRITERS_SQL + " WHERE " +
															WRITERS_TABLECOLUMN_USERNAME + " = ?"; // arg0 := readers table name; param1 := username
	protected static final String INSERT_READER_SQL		= "INSERT INTO %s (" + 
															READERS_TABLECOLUMN_USERNAME + ", " +
															READERS_TABLECOLUMN_NOTEBOOK_ID + ", " +
															READERS_TABLECOLUMN_KEY + ") VALUES (?,?,?)"; //arg0 := readers table name; param1 := username; param2 := notebook id; param3 := key
	protected static final String INSERT_WRITER_SQL		= "INSERT INTO %s (" + 
															WRITERS_TABLECOLUMN_USERNAME + ", " +
															WRITERS_TABLECOLUMN_NOTEBOOK_ID + ") VALUES (?,?)"; //arg0 := WRITERs table name; param1 := username; param2 := notebook id;
	protected static final String DELETE_READER_SQL		= "DELETE FROM %s WHERE " +
															READERS_TABLECOLUMN_USERNAME + " = ? AND " +
															READERS_TABLECOLUMN_NOTEBOOK_ID + " = ?"; //arg0 := readers table name; param1 := username; param2 := notebook id
	protected static final String DELETE_WRITER_SQL		= "DELETE FROM %s WHERE " +
															WRITERS_TABLECOLUMN_USERNAME + " = ? AND " +
															WRITERS_TABLECOLUMN_NOTEBOOK_ID + " = ?"; //arg0 := WRITERs table name; param1 := username; param2 := notebook id
	protected static final String GET_LAST_INSERT_ID	= "SELECT LAST_INSERT_ID()";
	
	protected int id = -1;
	protected String ownerUsername;
	protected Date   lastUpdated;
	protected String lockUsername;
	protected Date   lockDate;
	protected WeakReference<byte[]> data 	= new WeakReference<byte[]>(null);
	
	public static void setUpContext(DatabaseContext context) throws DatabaseException {
		if(initializedContexts.get(context) == null) {
			DBPerson.setUpContext(context);
			context.executeSQLNoResults(String.format(NOTEBOOKS_TABLE_DDL, 
														context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME), 
														context.getPrefixedTableName(DBPerson.PERSON_TABLE_NAME), 
														context.getPrefixedTableName(DBPerson.PERSON_TABLE_NAME)));

			context.executeSQLNoResults(String.format(READERS_TABLE_DDL, 
					 									context.getPrefixedTableName(READERS_TABLE_NAME),
					 									context.getPrefixedTableName(DBPerson.PERSON_TABLE_NAME),
					 									context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME)));
			
			context.executeSQLNoResults(String.format(WRITERS_TABLE_DDL, 
					  									context.getPrefixedTableName(WRITERS_TABLE_NAME),
					  									context.getPrefixedTableName(DBPerson.PERSON_TABLE_NAME),
					  									context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME)));
			initializedContexts.put(context, new HashMap<Integer, WeakReference<DBNotebook>>());
		}
	}
	
	public static void clearContext(DatabaseContext context) throws DatabaseException {
		if(initializedContexts.remove(context) != null) {
			context.dropTable(READERS_TABLE_NAME);
			context.dropTable(WRITERS_TABLE_NAME);
			context.dropTable(NOTEBOOKS_TABLE_NAME);
			DBPerson.clearContext(context);
		}
	}
	
	
	protected DBNotebook(DatabaseContext context) throws DatabaseException {
		super(context);
		DBNotebook.setUpContext(context);
	}
	
	protected DBNotebook(DatabaseContext context, int id) throws DatabaseException {
		this(context);
		this.id = id;
		DBNotebook.initializedContexts.get(context).put(new Integer(id), new WeakReference<DBNotebook>(this));
	}
	
	protected void initializeFromResultSetCurrentRow(ResultSet rs) throws DatabaseException {
		try {
			this.ownerUsername 	= rs.getString(NOTEBOOKS_TABLECOLUMN_OWNER);
			this.lastUpdated   	= rs.getDate(NOTEBOOKS_TABLECOLUMN_LASTUPD);
			this.lockUsername 	= rs.getString(NOTEBOOKS_TABLECOLUMN_LOCKUSER);
			this.lockDate     	= rs.getDate(NOTEBOOKS_TABLECOLUMN_LOCKDATE);
			this.initialized 	= true;
		} catch (SQLException e) {
			throw new DatabaseExecutionException("Error reading from result set", e);
		}
		
	}

	@Override
	protected void initializeFromContext() throws DatabaseException {
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(GET_INSTANCE_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME)));
			ps.setInt(1, this.id);
			ResultSet rs = ps.executeQuery();
			rs.last();
			if(rs.getRow() != 1) {
				throw new DatabaseExecutionException("Wrong number of results for object creation query");
			} else {
				this.initializeFromResultSetCurrentRow(rs);
			}
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Could not create object", e);
		}
		
	}

	@Override
	protected void writeToContext() throws DatabaseException {
		String sql = String.format(GET_INSTANCE_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setInt(1,this.id);
			ResultSet rs = ps.executeQuery();
			if(rs.last()) {
				if(rs.getRow() != 1) {
					throw new DatabaseObjectNotFoundException("Wrong number of results for object query");
				}
				rs.updateString(NOTEBOOKS_TABLECOLUMN_OWNER, this.ownerUsername);
				rs.updateTimestamp(NOTEBOOKS_TABLECOLUMN_LASTUPD, new java.sql.Timestamp(this.lastUpdated.getTime()));
				rs.updateString(NOTEBOOKS_TABLECOLUMN_LOCKUSER, this.lockUsername);
				rs.updateTimestamp(NOTEBOOKS_TABLECOLUMN_LOCKDATE, (this.lockDate == null ? null : new java.sql.Timestamp(this.lockDate.getTime())));
				byte[] d = data.get();
				if(d != null) {
					rs.updateBytes(NOTEBOOKS_TABLECOLUMN_DATA, d);
				}
				rs.updateRow();
				rs.close();
				ps.close();
			} else {
				ps.close();
				sql = String.format(INSERT_INSTANCE_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME));
				ps = context.getConnection().prepareStatement(sql);
				ps.setString(1, this.ownerUsername);
				ps.setTimestamp(2, new java.sql.Timestamp(this.lastUpdated.getTime()));
				ps.setBytes(3, this.data.get());
				ps.executeUpdate();
				ps.close();
				
				//fetch id back
				ps = context.getConnection().prepareStatement(GET_LAST_INSERT_ID);
				rs = ps.executeQuery();
				rs.first();
				this.id = rs.getInt(1);
				rs.close();
				ps.close();
			}
			this.invalidate();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Could not write object", e);
		}
		
	}
	
	@Override
	public void invalidate() {
		super.invalidate();
		data.clear();
	};
	
	public int getId() {
		return id;
	}
	
	public byte[] getData() throws DatabaseException {
		this.initializeIfNeeded();
		byte[] d = this.data.get();
		if(d == null) {
			try {
				PreparedStatement ps = context.getConnection().prepareStatement(String.format(GET_DATA_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME)));
				ps.setInt(1, this.id);
				ResultSet rs = ps.executeQuery();
				rs.last();
				if(rs.getRow() != 1) {
					throw new DatabaseExecutionException("Wrong number of results for object query");
				} else {
					d = rs.getBytes(NOTEBOOKS_TABLECOLUMN_DATA);
				}
			} catch (Exception e) {
				throw new DatabaseObjectNotFoundException("Could not find object", e);
			}
		}
		
		return d;
	}

	public void setData(byte[] data) throws DatabaseException {
		this.initializeIfNeeded();
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(SET_DATA_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME)));
			ps.setBytes(1, data);
			ps.setInt(2, this.id);
			ps.executeUpdate();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem updating object in database", e);
		}
	}
	
	public DBPerson getOwner() throws DatabaseException {
		this.initializeIfNeeded();
		return DBPerson.getPersonByUsername(this.context, this.ownerUsername);
	}
	
	public boolean isLocked() throws DatabaseException {
		this.initializeIfNeeded();
		return (this.lockUsername != null && this.lockUsername.length() > 0);
	}
	
	public DBPerson getLockPerson() throws DatabaseException {
		return (this.isLocked() ? DBPerson.getPersonByUsername(this.context, this.lockUsername) : null);
	}
	
	public Date getLockDate() throws DatabaseException {
		return (this.isLocked() ? this.lockDate : null);
	}
	
	public Date getLastUpdatedDate() throws DatabaseException {
		this.initializeIfNeeded();
		return this.lastUpdated;
	}
	
	public void setLock(DBPerson lockUser, Date lockDate) throws DatabaseExecutionException {
		String sql = String.format(UPDATE_LOCK_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(1, lockUser.getUsername());
			ps.setTimestamp(2, new java.sql.Timestamp(lockDate.getTime()));
			ps.setInt(3, this.id);
			ps.executeUpdate();
		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem setting lock, SQL: " + sql, e);
		}
		
		this.invalidate();
	}
	
	public void unlock() throws DatabaseExecutionException {
		String sql = String.format(UPDATE_LOCK_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setNull(1, java.sql.Types.VARCHAR);
			ps.setNull(2, java.sql.Types.TIMESTAMP);
			ps.setInt(3, this.id);
			ps.executeUpdate();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem clearing lock", e);
		}
		this.invalidate();
	}

	
	public DBPerson[] getReaders() throws DatabaseObjectNotFoundException {
		DBPerson[] readers = null;
		String sql = String.format(GET_READERS_SQL, context.getPrefixedTableName(READERS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setInt(1, this.id);
			ResultSet rs = ps.executeQuery();
			rs.last();
			readers = new DBPerson[rs.getRow()];
			rs.beforeFirst();
			for(int i = 0; rs.next(); i++) {
				String username = rs.getString(READERS_TABLECOLUMN_USERNAME);
				readers[i] = DBPerson.getPersonByUsername(context, username);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem enumerating readers, SQL: " + sql, e);
		}
		return readers;
	}
	
	public static DBNotebook[] getNotebooksReadableByPerson(DatabaseContext context, DBPerson person) throws DatabaseException {
		DBNotebook.setUpContext(context);
		DBNotebook[] notebooks = null;
		String sql = String.format(GET_ALL_NB_READER_SQL, context.getPrefixedTableName(READERS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(1, person.username);
			ResultSet rs = ps.executeQuery();
			rs.last();
			if(rs.getRow() < 1) {
				rs.close();
				ps.close();
				return null;
			}
			notebooks = new DBNotebook[rs.getRow()];
			rs.beforeFirst();
			for(int i = 0; rs.next(); i++) {
				int nbId = rs.getInt(READERS_TABLECOLUMN_NOTEBOOK_ID);
				notebooks[i] = DBNotebook.getNotebookById(context, nbId);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem enumerating notebooks, SQL: " + sql, e);
		}
		return notebooks;
	}
	
	public void addReader(DBPerson reader, byte[] key) throws DatabaseObjectNotFoundException {
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(INSERT_READER_SQL, context.getPrefixedTableName(READERS_TABLE_NAME)));
			ps.setString(1, reader.getUsername());
			ps.setInt(2, this.id);
			ps.setBytes(3, key);
			ps.executeUpdate();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem adding reader", e);
		}
	}
	
	public void removeReader(DBPerson reader) throws DatabaseObjectNotFoundException {
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(DELETE_READER_SQL, context.getPrefixedTableName(READERS_TABLE_NAME)));
			ps.setString(1, reader.getUsername());
			ps.setInt(2, this.id);
			ps.execute();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem deleting reader", e);
		}
	}
	
	public boolean hasReader(DBPerson reader) throws DatabaseObjectNotFoundException {
		return (this.getKeyForReader(reader) != null);
	}
	
	public byte[] getKeyForReader(DBPerson reader) throws DatabaseObjectNotFoundException {
		byte[] key = null;
		String sql = String.format(GET_READER_KEY_SQL, context.getPrefixedTableName(READERS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(2, reader.getUsername());
			ps.setInt(1, this.getId());
			ResultSet rs = ps.executeQuery();
			rs.last();
			if(rs.getRow() != 1) {
				throw new DatabaseExecutionException("Wrong number of results for object query");
			}
			key = rs.getBytes(READERS_TABLECOLUMN_KEY);
			rs.close();
			ps.close();
		} catch (Exception e) {
			//throw new DatabaseObjectNotFoundException("Problem enumerating readers, SQL: " + sql, e);
			key = null;		
		}
		return key;
	}
	
	public DBPerson[] getWriters() throws DatabaseObjectNotFoundException {
		DBPerson[] writers = null;
		String sql = String.format(GET_WRITERS_SQL, context.getPrefixedTableName(WRITERS_TABLE_NAME));
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setInt(1, this.id);
			ResultSet rs = ps.executeQuery();
			rs.last();
			writers = new DBPerson[rs.getRow()];
			rs.beforeFirst();
			for(int i = 0; rs.next(); i++) {
				String username = rs.getString(READERS_TABLECOLUMN_USERNAME);
				writers[i] = DBPerson.getPersonByUsername(context, username);
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem enumerating writers SQL: " + sql, e);
		}
		return writers;
	}
	
	public boolean hasWriter(DBPerson writer) throws DatabaseException  {
		//short circuit for owner
		if(this.getOwner() == writer) {
			return true;
		}
		
		String sql = String.format(GET_IS_WRITER_SQL, context.getPrefixedTableName(WRITERS_TABLE_NAME));
		boolean isWriter = false;
		try {
			//arg0 := WRITERs table name; param1 := notebook id; param2 := username
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ps.setString(2, writer.getUsername());
			ps.setInt(1, this.getId());
			ResultSet rs = ps.executeQuery();
			rs.last();
			isWriter = rs.getRow() != 0;
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem enumerating writers SQL: " + sql, e);
		}
		return isWriter;
	}

	
	public void addWriter(DBPerson writer) throws DatabaseObjectNotFoundException {
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(INSERT_WRITER_SQL, context.getPrefixedTableName(WRITERS_TABLE_NAME)));
			ps.setString(1, writer.getUsername());
			ps.setInt(2, this.id);
			ps.executeUpdate();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem adding writer", e);
		}
	}
	
	public void removeWriter(DBPerson writer) throws DatabaseObjectNotFoundException {
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(String.format(DELETE_WRITER_SQL, context.getPrefixedTableName(WRITERS_TABLE_NAME)));
			ps.setString(1, writer.getUsername());
			ps.setInt(2, this.id);
			ps.execute();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseObjectNotFoundException("Problem deleting writer", e);
		}
	}
	
	
	public static DBNotebook getNotebookById(DatabaseContext context, int id) throws DatabaseException {
		DBNotebook n = DBNotebook.getLoadedNotebookById(context, id);
		return (n != null ? n : new DBNotebook(context, id));
	}
	
	public static DBNotebook getLoadedNotebookById(DatabaseContext context, int id) throws DatabaseException {
		DBNotebook.setUpContext(context);
		DBNotebook n = null;
		Map<Integer, WeakReference<DBNotebook>> nMap = DBNotebook.initializedContexts.get(context);
		WeakReference<DBNotebook> wp = nMap.get(new Integer(id));
		if(wp != null) {
			n = wp.get();
			
			//remove expired entries
			if(n == null) {
				nMap.remove(wp);
			}
		}
		return n;
	}
	
	public static DBNotebook[] getAllNotebooks(DatabaseContext context) throws DatabaseException {
		DBNotebook.setUpContext(context);
		String sql = String.format(GET_ALL_INSTANCES_SQL, context.getPrefixedTableName(NOTEBOOKS_TABLE_NAME));
		DBNotebook[] notebooks = null;
		try {
			PreparedStatement ps = context.getConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			rs.last();
			notebooks = new DBNotebook[rs.getRow()];
			rs.beforeFirst();
			for(int i = 0; rs.next(); i++) {
				int id = rs.getInt(NOTEBOOKS_TABLECOLUMN_ID);
				DBNotebook n = DBNotebook.getLoadedNotebookById(context, id);
				
				//if n is null, then id is not loaded.  Create a new notebook.
				//then, since all of the data is already here, initialize the object right away.
				if(n == null) {
					n = new DBNotebook(context, id);
					n.initializeFromResultSetCurrentRow(rs);
				}
				
				notebooks[i] = n;
			}
			rs.close();
			ps.close();
		} catch (Exception e) {
			throw new DatabaseExecutionException("Problem enumerating objects", e);
		}
		return notebooks;
	}
	
	public static DBNotebook createNotebook(DatabaseContext context, DBPerson owner, Date createDate, byte[] data) throws DatabaseException {
		DBNotebook newNotebook = new DBNotebook(context);
		newNotebook.ownerUsername = owner.getUsername();
		newNotebook.lastUpdated = createDate;
		newNotebook.data = new WeakReference<byte[]>(data);
		
		newNotebook.writeToContext();
		DBNotebook.initializedContexts.get(context).put(new Integer(newNotebook.getId()), new WeakReference<DBNotebook>(newNotebook));
		return newNotebook;
	}
	
}
